package com.atguigu3.preparedstatement.crud;

import com.atguigu3.bean.Customer;
import com.atguigu3.util.JDBCUtils;
import org.junit.Test;

import java.lang.reflect.Field;
import java.sql.*;

/**
 * 针对Customer表的查询操作
 */
public class CustomerForQuery {
    @Test
    public void testQueryForCustomers() {
        String sql = "select id,name,email,birth from customers where id = ?";
        Customer customer = queryForCustomers(sql, 13);
        System.out.println(customer);

        sql = "select name,email from customers where name = ?";
        Customer customer1 = queryForCustomers(sql, "周杰伦");
        System.out.println(customer1);
    }

    //针对Customers表通用的查询操作
    public Customer queryForCustomers(String sql, Object... args) {
        //public Customer queryForCustomers(String sql, Object... args) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            //1.连接数据库
            conn = JDBCUtils.getConnection();
            //2.预编译sql语句
            ps = conn.prepareStatement(sql);
            //3.填充占位符
            for (int i = 0; i < args.length; i++) {
                ps.setObject(i + 1, args[i]);
            }
            //4.执行
            rs = ps.executeQuery();
            //5.获取结果集的元数据；ResultSetMetaDate
            // String name = "Tom"当中String就可以看做是“Tom”的元数据
            ResultSetMetaData rsmd = rs.getMetaData();
            //6.通过获取ResultSetMetaDate获取结果集中的列数
            //conn -> ps -> rs -> rsmd -> columnCount
            int columnCount = rsmd.getColumnCount();

            if (rs.next()) {
                Customer cust = new Customer();
                //处理结果集一行数据中的每一个列
                for (int i = 0; i < columnCount; i++) {
                    //7.获取每个列的值
                    Object columnValue = rs.getObject(i + 1);

                    //8.获取每个列的列名
                    //String columnName = rsmd.getColumnName(i + 1);
                    String columnLabel = rsmd.getColumnLabel(i + 1);

                    //9.给cust对象指定的columnName属性赋值为columnValue：通过反射
                    Field field = Customer.class.getDeclaredField(columnLabel);
                    field.setAccessible(true);
                    field.set(cust, columnValue);
                }
                return cust;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(conn, ps, rs);
        }
        return null;
    }


    @Test
    public void testQuery1() {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            //1.连接数据库
            conn = JDBCUtils.getConnection();
            //2.预编译sql语句
            String sql = "select id,name,email,birth from customers where id = ?";
            ps = conn.prepareStatement(sql);
            ps.setObject(1, 1);

            //3.执行,并返回结果集
            rs = ps.executeQuery();
            //4.处理结果集
            if (rs.next()) {//判断结果集的下一条是否有数据，如果有就指针下一并返回true，如果没有就不再下移，返回false
                //获取当前这条数据的各个字段
                int id = rs.getInt(1);
                String name = rs.getString(2);
                String email = rs.getString(3);
                Date birth = rs.getDate(4);

                //方式一:直接输出
                //System.out.println("id = " + id + ",name = " + name + ",email = " + email + ".birth = " + birth );

                //方式二:封装到集合中
                //Object[] data = new Object[]{id,name,email,birth};

                //方式三:将数据封装成一个对象(推荐)
                Customer customer = new Customer(id, name, email, birth);
                System.out.println(customer);
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            //5.关闭资源
            JDBCUtils.closeResource(conn, ps, rs);
        }
    }
}
